using AutoMapper;
using Dapper;
using MediatR;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;
using RBAC.Domain.Dto.Output;
using RBAC.Domain.PurchasingManagement.RawMaterialSupply;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.RawMaterialSuppliers;
using System.Linq;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.RawMaterialSuppliers
{
    /// <summary>
    /// 供应商列表查询（Dapper）
    /// </summary>
    public class GetRawMaterialSuppliersListHandler : IRequestHandler<GetRawMaterialSuppliersListCommand, APIPaging<GetRawMaterialSupplierListDto>>
    {
        private readonly IBaseRepository<Supplier> supplierRepository;
   
        private readonly ILogger<GetRawMaterialSuppliersListHandler> logger;
        private readonly IMapper mapper;

        public GetRawMaterialSuppliersListHandler(
            IBaseRepository<Supplier> supplierRepository,
       
            IMapper mapper,
            ILogger<GetRawMaterialSuppliersListHandler> logger)
        {
            this.supplierRepository = supplierRepository;
         
            this.mapper = mapper;
            this.logger = logger;
        }

        public Task<APIPaging<GetRawMaterialSupplierListDto>> Handle(GetRawMaterialSuppliersListCommand request, CancellationToken cancellationToken)
        {
            var res = new APIPaging<GetRawMaterialSupplierListDto>();
            try
            {
                // 基础SQL（Supplier 左连接 SupplierBusiness、SupplierFinance、SupplierInvoice 四表）
                var fromSql = @" FROM Supplier s
                            LEFT JOIN SupplierBusiness sb ON s.Id = sb.Supplier_Id AND sb.IsDel = 0
                            LEFT JOIN SupplierFinance sf ON s.Id = sf.Supplier_Id AND sf.IsDel = 0
                            LEFT JOIN SupplierInvoice si ON s.Id = si.Supplier_Id AND si.IsDel = 0
                            WHERE s.IsDeleted = 0";

                // 条件与参数
                var whereParts = new List<string>();
                var param = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.Supplier_Name))
                {
                    whereParts.Add("s.Supplier_Name LIKE @Supplier_Name");
                    param.Add("Supplier_Name", $"%{request.Supplier_Name}%");
                }
                if (!string.IsNullOrWhiteSpace(request.BusinessContact_Name))
                {
                    whereParts.Add("sb.Contact_Name LIKE @BusinessContact_Name");
                    param.Add("BusinessContact_Name", $"%{request.BusinessContact_Name}%");
                }
                if (!string.IsNullOrWhiteSpace(request.Supply_Products))
                {
                    whereParts.Add("s.Supply_Products = @Supply_Products");
                    param.Add("Supply_Products", request.Supply_Products);
                }

                var whereSql = whereParts.Count > 0 ? (" AND " + string.Join(" AND ", whereParts)) : string.Empty;

                // 1) 统计总数（去重按供应商）
                var countSql = "SELECT COUNT(DISTINCT s.Id)" + fromSql + whereSql;
                var total = supplierRepository.Query<int>(countSql, param).FirstOrDefault();

                // 若无数据，直接返回空分页
                if (total == 0)
                {
                    res.Code = APIEnums.Success;
                    res.TotalCount = 0;
                    res.PageDatas = new List<GetRawMaterialSupplierListDto>();
                    res.Msg = "获取供应商列表成功";
                    return Task.FromResult(res);
                }

                // 2) 获取当前页的供应商Id集合（去重+排序+分页）
                var offset = (request.PageIndex - 1) * request.PageSize;
                if (offset < 0) offset = 0;
                param.Add("Offset", offset);
                param.Add("PageSize", request.PageSize);

                var idsSql = "SELECT DISTINCT s.Id" + fromSql + whereSql + " ORDER BY s.Id DESC LIMIT @Offset, @PageSize";
                var pageIds = supplierRepository.Query<long>(idsSql, param).ToList();

                if (pageIds.Count == 0)
                {
                    res.Code = APIEnums.Success;
                    res.TotalCount = total;
                    res.PageDatas = new List<GetRawMaterialSupplierListDto>();
                    res.Msg = "获取供应商列表成功";
                    return Task.FromResult(res);
                }

                // 3) 拉取该页供应商的详细联查数据，并聚合
                var dataSql = @"SELECT 
                                s.Id,
                                s.Supplier_Name,
                                s.Supplier_Alias,
                                s.Short_Name,
                                s.Province_City,
                                s.Supply_Category,
                                s.Supplier_Level,
                                s.Supply_Products,
                                s.Company_Address,
                                s.Postal_Code,
                                s.Phone,
                                s.Remarks,
                                s.Status,
                                sb.Contact_Name AS BusinessContact_Name,
                                sb.Mobile_Phone AS BusinessContact_Phone,
                                sf.Contact_Name AS FinanceContact_Name,
                                sf.Mobile_Phone AS FinanceContact_Phone,
                                si.Bank_Name,
                                si.Bank_Address,
                                si.Account_Number,
                                si.Tax_Number
                            FROM Supplier s
                            LEFT JOIN SupplierBusiness sb ON s.Id = sb.Supplier_Id AND sb.IsDel = 0
                            LEFT JOIN SupplierFinance sf ON s.Id = sf.Supplier_Id AND sf.IsDel = 0
                            LEFT JOIN SupplierInvoice si ON s.Id = si.Supplier_Id AND si.IsDel = 0
                            WHERE s.Id IN @Ids
                            ORDER BY s.Id DESC";
                var dataRows = supplierRepository.Query<dynamic>(dataSql, new { Ids = pageIds });

                // 分组聚合联系人与开票信息
                var grouped = dataRows
                    .GroupBy(x => new
                    {
                        Id = (long)x.Id,
                        Supplier_Name = (string)x.Supplier_Name,
                        Supplier_Alias = (string)x.Supplier_Alias,
                        Short_Name = (string)x.Short_Name,
                        Province_City = (string)x.Province_City,
                        Supply_Category = (string)x.Supply_Category,
                        Supplier_Level = (string)x.Supplier_Level,
                        Supply_Products = (string)x.Supply_Products,
                        Company_Address = (string)x.Company_Address,
                        Postal_Code = (string)x.Postal_Code,
                        Phone = (string)x.Phone,
                        Remarks = (string)x.Remarks,
                        Status = (int)x.Status
                    })
                    .Select(g => new GetRawMaterialSupplierListDto
                    {
                        Id = g.Key.Id,
                        Supplier_Name = g.Key.Supplier_Name,
                        Supplier_Alias = g.Key.Supplier_Alias,
                        Short_Name = g.Key.Short_Name,
                        Province_City = g.Key.Province_City,
                        Supply_Category = g.Key.Supply_Category,
                        Supplier_Level = g.Key.Supplier_Level,
                        Supply_Products = g.Key.Supply_Products,
                        Company_Address = g.Key.Company_Address,
                        Postal_Code = g.Key.Postal_Code,
                        Phone = g.Key.Phone,
                        Remarks = g.Key.Remarks,
                        Status = g.Key.Status,
                        BusinessContact = g.Where(r => r.BusinessContact_Name != null).Select(r => (string)r.BusinessContact_Name).FirstOrDefault(),
                        BusinessContact_Phone = g.Where(r => r.BusinessContact_Phone != null).Select(r => (string)r.BusinessContact_Phone).FirstOrDefault(),
                        FinanceContact = g.Where(r => r.FinanceContact_Name != null).Select(r => (string)r.FinanceContact_Name).FirstOrDefault(),
                        FinanceContact_Phone = g.Where(r => r.FinanceContact_Phone != null).Select(r => (string)r.FinanceContact_Phone).FirstOrDefault(),
                        Bank_Name = g.Select(r => (string)r.Bank_Name).FirstOrDefault(s => !string.IsNullOrEmpty(s)),
                        Bank_Address = g.Select(r => (string)r.Bank_Address).FirstOrDefault(s => !string.IsNullOrEmpty(s)),
                        Account_Number = g.Select(r => (string)r.Account_Number).FirstOrDefault(s => !string.IsNullOrEmpty(s)),
                        Tax_Number = g.Select(r => (string)r.Tax_Number).FirstOrDefault(s => !string.IsNullOrEmpty(s))
                    })
                    .ToList();

                res.Code = APIEnums.Success;
                res.TotalCount = total;
                res.PageDatas = grouped;
                res.Msg = "获取供应商列表成功";
            }
            catch (Exception ex)
            {
                res.Code = APIEnums.Error;
                res.Msg = "获取供应商列表失败";
                logger.LogError(ex, "获取供应商列表失败: {Message}", ex.Message);
            }

            return Task.FromResult(res);
        }
    }
}
